﻿using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Modules.CheckIn;
using Modules.CheckIn.Entitys;
using Modules.Project.Entitys;
using Modules.System.User;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.Caching;
using PmSoft.DBContext;
using PmSoft.Entity;
using PmSoft.Repositories;
using PmSoft.Utilities;

namespace Modules.Project.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class ProjectGroupMemberRepository : Repository<ProjectGroupMember, DefaultDbContext>
    {
        private readonly ILogger logger;
        private readonly IApplicationContext applicationContext;

        public ProjectGroupMemberRepository(DefaultDbContext dbContext, IApplicationContext applicationContext, ILogger<ProjectGroupMemberRepository> logger)
            : base(dbContext, applicationContext)
        {
            this.logger = logger;
            this.applicationContext = applicationContext;
        }

        public bool ExistsByProjectPerson(ProjectGroupMember projectGroupMember, int? id = null)
        {
            if (id.HasValue)
                return DbContext.Exists<ProjectGroupMember>("project_id=@0 AND person_id=@1 AND group_id=@2 AND is_manager=0 AND id!=@3", projectGroupMember.ProjectId, projectGroupMember.PersonId, projectGroupMember.GroupId, id);
            else return DbContext.Exists<ProjectGroupMember>("project_id=@0 AND  group_id=@1 AND is_manager=0 AND person_id=@2", projectGroupMember.ProjectId, projectGroupMember.GroupId, projectGroupMember.PersonId);
        }

        public IPagedList<ProjectGroupMember> GetProjectGroupMembersByManager(ProjectGroupMemberQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("tt.id")
                .From("project_group_member tt");
            sql.InnerJoin("person as b").On("tt.person_id=b.person_id");
            sql.InnerJoin("corp as d").On("tt.corp_id=d.corp_id");

            #region 主管单位数据权限

            if (applicationContext.CurrentUser is IDeptUser deptUser && !deptUser.IsSuperAdministrator())
            {
                sql.InnerJoin("project as prj").On($"tt.project_id=prj.project_id and prj.is_deleted=0 and prj.industry_type=@0 ", deptUser.IndustryType);
                if (deptUser.Level == AdministrativeLevel.City)
                    sql.Append($" and prj.city_code={deptUser.CityCode}");
                if (deptUser.Level == AdministrativeLevel.Area)
                    sql.Append($" and prj.area_code={deptUser.AreaCode}");
            }

            #endregion 主管单位数据权限

            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("(b.name like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            if (query.ProjectId.HasValue)
                sql.Where("tt.project_id=@0", query.ProjectId);
            if (query.IsManager.HasValue)
            {
                if (query.IsManager == true)
                    sql.Where("tt.is_manager=@0", query.IsManager);
            }
            if (query.CorpId.HasValue)
                sql.Where("tt.corp_id=@0", query.CorpId);
            sql.OrderBy("tt.create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        public IPagedList<ProjectGroupMember> GetProjectGroupMembers(ProjectGroupMemberQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("b.id")
                .From("person as a");
            sql.InnerJoin("project_group_member as b").On("a.person_id=b.person_id");
            sql.InnerJoin("project_group as c").On("b.group_id=c.group_id");
            sql.InnerJoin("corp as d").On("b.corp_id=d.corp_id");
            sql.LeftJoin("person_bank as e").On("a.card_number=e.idcard");
            sql.LeftJoin("person_corp_contract as f").On("a.person_id=f.person_id and f.corp_id=d.corp_id and b.project_id=f.project_id");

            #region 主管单位数据权限

            if (applicationContext.CurrentUser is IDeptUser deptUser && !deptUser.IsSuperAdministrator())
            {
                sql.InnerJoin("project as prj").On($"b.project_id=prj.project_id and prj.is_deleted=0 and prj.industry_type=@0 ", deptUser.IndustryType);
                if (deptUser.Level == AdministrativeLevel.City)
                    sql.Append($" and prj.city_code={deptUser.CityCode}");
                if (deptUser.Level == AdministrativeLevel.Area)
                    sql.Append($" and prj.area_code={deptUser.AreaCode}");
            }

            #endregion 主管单位数据权限

            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("(c.name Like @0 or a.name like @0 or e.card_no like @0 or e.idcard like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            if (query.ProjectId.HasValue)
                sql.Where("b.project_id=@0", query.ProjectId);
            if (query.GroupId.HasValue)
                sql.Where("b.group_id=@0", query.GroupId);
            if (query.CorpId.HasValue)
                sql.Where("b.corp_id=@0", query.CorpId);
            //if (query.IsManager.HasValue)
            //    sql.Where("b.is_manager=@0", query.IsManager.Value ? 1 : 0);
            if (query.HasBankCard.HasValue)
            {
                if (query.HasBankCard == true)
                    sql.Where("e.card_no is not null");
                else
                    sql.Where("e.card_no is null");
            }
            if (query.HasContract.HasValue)
            {
                if (query.HasContract == true)
                    sql.Where("f.signing_date is not null");
                else
                    sql.Where("f.signing_date is null");
            }
            if (query.Type.HasValue)
                sql.Where("b.type=@0", query.Type.Value);
            if (query.CardType.HasValue)
                sql.Where("e.type=@0", query.CardType.Value);
            if (query.Status.HasValue)
                sql.Where("b.status=@0", query.Status.Value);
            sql.OrderBy("a.create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByGroupIds(IEnumerable<int> groupIds)
        {
            if (groupIds == null || groupIds.Count() == 0)
                return Enumerable.Empty<ProjectGroupMember>();
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_group_member");
            if (groupIds.Count() > 0)
                sql.Where("group_id in(@0)", groupIds);
            //sql.Where("is_manager=0");
            var ids = DbContext.FetchFirstColumn<int>(sql);

            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByCorpIds(IEnumerable<int> corpIds)
        {
            if (corpIds == null || corpIds.Count() == 0)
                return Enumerable.Empty<ProjectGroupMember>();
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_group_member");
            if (corpIds.Count() > 0)
                sql.Where("corp_id in(@0)", corpIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);

            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByPersonIds(IEnumerable<int> personIds)
        {
            if (personIds == null || personIds.Count() == 0)
                return Enumerable.Empty<ProjectGroupMember>();
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_group_member");
            sql.Where("person_id in(@0)", personIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }

        public bool HasLeader(int? groupId, int? id)
        {
            if (!groupId.HasValue)
                return false;
            if (!id.HasValue)
                return DbContext.Exists<ProjectGroupMember>("group_id=@0 AND is_leader=1 AND id<>@1", groupId, id.Value);
            else
                return DbContext.Exists<ProjectGroupMember>("group_id=@0 AND is_leader=1", groupId);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByProjectId(int projectId)
        {
            Sql sql = Sql.Builder
                  .Select("id")
                  .From("project_group_member")
                  .Where("project_id=@0", projectId);
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByGroupId(int groupId)
        {
            int areaVersion = EntityCacheService.GetAreaVersion("GroupId", groupId);
            return GetTopEntities(1000, CachingExpirationType.ObjectCollection, () =>
            {
                return $"ProjectGroupMembersByGroupId:{groupId}:{areaVersion}";
            }, () =>
            {
                Sql sql = Sql.Builder
                .Select("id")
                .From("project_group_member")
                .Where("group_id=@0", groupId);
                return sql;
            });
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByPersonId(int personId)
        {
            int areaVersion = EntityCacheService.GetAreaVersion("PersonId", personId);
            return GetTopEntities(1000, CachingExpirationType.ObjectCollection, () =>
            {
                return $"ProjectGroupMembersByPersonId:{personId}:{areaVersion}";
            }, () =>
            {
                Sql sql = Sql.Builder
                .Select("id")
                .From("project_group_member")
                .Where("person_id=@0", personId);
                return sql;
            });
        }

        public int GetProjectPersonCount(ProjectCheckinCountQuery query)
        {
            if (!query.ProjectId.HasValue) return 0;
            Sql sql = Sql.Builder;
            sql.Select("count(1)")
                .From("project_group_member");
            if (query.ProjectId.HasValue)
                sql.Where("project_id =@0", query.ProjectId.Value);
            if (!string.IsNullOrEmpty(query.GroupIds))
                sql.Where("group_id  in(@0)", ValueUtility.ConvertToList<int>(query.GroupIds));
            return DbContext.ExecuteScalar<int>(sql);
        }

        public IEnumerable<PersonCount> GetPersonCountsByProjectId(ProjectCheckinCountQuery query)
        {
            Sql sql = Sql.Builder;
            sql.Select("COUNT(DISTINCT person_id) as count,group_id")
                .From("project_group_member");
            if (query.ProjectId.HasValue)
                sql.Where("project_id =@0", query.ProjectId.Value);
            if (!string.IsNullOrEmpty(query.GroupIds))
                sql.Where("group_id  in(@0)", ValueUtility.ConvertToList<int>(query.GroupIds));
            sql.GroupBy("group_id");
            return DbContext.Fetch<PersonCount>(sql);
        }

        public IEnumerable<ProjectGroupMember> GetProjectGroupMembersByProjectIds(IEnumerable<int> projectIds)
        {
            if (projectIds == null || projectIds.Count() == 0)
                return Enumerable.Empty<ProjectGroupMember>();
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("project_group_member");
            sql.Where("project_id in(@0)", projectIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);
            return PopulateEntitiesByEntityIds(ids);
        }
    }
}